package com.edu.dao;

import com.edu.bean.Post;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PostDao {

    /**
     * 分页查询帖子
     */
    public List<Post> findByPage(int start, int limit, String title, Integer type) {
        List<Post> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT p.*, u.username FROM post p " +
                        "LEFT JOIN user u ON p.user_id = u.user_id WHERE 1=1 "
        );
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql.append("AND p.title LIKE ? ");
            params.add("%" + title + "%");
        }
        if (type != null) {
            sql.append("AND p.post_type = ? ");
            params.add(type);
        }

        sql.append("ORDER BY p.post_id DESC LIMIT ?, ?");
        params.add(start);
        params.add(limit);

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                list.add(convertToPost(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(String title, Integer type) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM post WHERE 1=1 ");
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql.append("AND title LIKE ? ");
            params.add("%" + title + "%");
        }
        if (type != null) {
            sql.append("AND post_type = ? ");
            params.add(type);
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据ID查询帖子
     */
    public Post findById(Integer postId) {
        String sql = "SELECT p.*, u.username FROM post p " +
                "LEFT JOIN user u ON p.user_id = u.user_id WHERE p.post_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, postId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToPost(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 删除帖子
     */
    public boolean delete(Integer postId) {
        String sql = "DELETE FROM post WHERE post_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, postId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 增加浏览次数
     */
    public void incrementViewCount(Integer postId) {
        String sql = "UPDATE post SET view_count = view_count + 1 WHERE post_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, postId);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加帖子
     */
    public boolean add(Post post) {
        String sql = "INSERT INTO post (user_id, title, content, post_type, view_count, create_time) " +
                "VALUES (?, ?, ?, ?, 0, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, post.getUserId());
            stmt.setString(2, post.getTitle());
            stmt.setString(3, post.getContent());
            stmt.setInt(4, post.getPostType());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 分页查询学生帖子（包含用户信息）
     */
    public List<Map<String, Object>> findStudentPostsWithUser(int start, int pageSize) {
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "SELECT p.*, u.username FROM post p " +
                "LEFT JOIN user u ON p.user_id = u.user_id " +
                "WHERE p.post_type = 1 " + // 1=学生帖子
                "ORDER BY p.post_id DESC LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, start);
            stmt.setInt(2, pageSize);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("post_id", rs.getInt("post_id"));
                map.put("user_id", rs.getInt("user_id"));
                map.put("title", rs.getString("title"));
                map.put("content", rs.getString("content"));
                map.put("post_type", rs.getInt("post_type"));
                map.put("view_count", rs.getInt("view_count"));
                map.put("create_time", rs.getTimestamp("create_time"));
                map.put("update_time", rs.getTimestamp("update_time"));
                map.put("username", rs.getString("username"));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询学生帖子总数
     */
    public int findStudentPostsTotalCount() {
        String sql = "SELECT COUNT(*) FROM post WHERE post_type = 1"; // 1=学生帖子
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询帖子详情（包含用户信息）
     */
    public Map<String, Object> findPostWithUser(Integer postId) {
        String sql = "SELECT p.*, u.username FROM post p " +
                "LEFT JOIN user u ON p.user_id = u.user_id " +
                "WHERE p.post_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, postId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("post_id", rs.getInt("post_id"));
                map.put("user_id", rs.getInt("user_id"));
                map.put("title", rs.getString("title"));
                map.put("content", rs.getString("content"));
                map.put("post_type", rs.getInt("post_type"));
                map.put("view_count", rs.getInt("view_count"));
                map.put("create_time", rs.getTimestamp("create_time"));
                map.put("update_time", rs.getTimestamp("update_time"));
                map.put("username", rs.getString("username"));
                return map;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 将ResultSet转换为Post对象
     */
    private Post convertToPost(ResultSet rs) throws SQLException {
        Post post = new Post();
        post.setPostId(rs.getInt("post_id"));
        post.setUserId(rs.getInt("user_id"));
        post.setTitle(rs.getString("title"));
        post.setContent(rs.getString("content"));
        post.setPostType(rs.getInt("post_type"));
        post.setViewCount(rs.getInt("view_count"));
        post.setCreateTime(rs.getTimestamp("create_time"));
        post.setUpdateTime(rs.getTimestamp("update_time"));
        post.setUsername(rs.getString("username"));
        return post;
    }

    /**
     * 分页查询用户的心理咨询帖子
     */
    public List<Map<String, Object>> findConsultPostsByUser(Integer userId, int start, int pageSize) {
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "SELECT p.*, u.username, " +
                "(SELECT COUNT(*) FROM post_reply WHERE post_id = p.post_id) as reply_count " +
                "FROM post p " +
                "LEFT JOIN user u ON p.user_id = u.user_id " +
                "WHERE p.user_id = ? AND p.post_type = 2 " + // 2=心理咨询
                "ORDER BY p.post_id DESC LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            stmt.setInt(2, start);
            stmt.setInt(3, pageSize);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("post_id", rs.getInt("post_id"));
                map.put("user_id", rs.getInt("user_id"));
                map.put("title", rs.getString("title"));
                map.put("content", rs.getString("content"));
                map.put("post_type", rs.getInt("post_type"));
                map.put("view_count", rs.getInt("view_count"));
                map.put("create_time", rs.getTimestamp("create_time"));
                map.put("update_time", rs.getTimestamp("update_time"));
                map.put("username", rs.getString("username"));
                map.put("reply_count", rs.getInt("reply_count"));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询用户的心理咨询总数
     */
    public int findConsultPostsTotalCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM post WHERE user_id = ? AND post_type = 2"; // 2=心理咨询
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询用户已回复的心理咨询数量
     */
    public int findRepliedConsultCount(Integer userId) {
        String sql = "SELECT COUNT(DISTINCT p.post_id) FROM post p " +
                "INNER JOIN post_reply r ON p.post_id = r.post_id " +
                "WHERE p.user_id = ? AND p.post_type = 2"; // 2=心理咨询
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询用户待回复的心理咨询数量
     */
    public int findWaitingConsultCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM post p " +
                "WHERE p.user_id = ? AND p.post_type = 2 " + // 2=心理咨询
                "AND NOT EXISTS (SELECT 1 FROM post_reply r WHERE r.post_id = p.post_id)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 分页查询用户的就业指导帖子
     */
    public List<Map<String, Object>> findCareerPostsByUser(Integer userId, int start, int pageSize) {
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "SELECT p.*, u.username, " +
                "(SELECT COUNT(*) FROM post_reply WHERE post_id = p.post_id) as reply_count " +
                "FROM post p " +
                "LEFT JOIN user u ON p.user_id = u.user_id " +
                "WHERE p.user_id = ? AND p.post_type = 3 " + // 3=就业指导
                "ORDER BY p.post_id DESC LIMIT ?, ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            stmt.setInt(2, start);
            stmt.setInt(3, pageSize);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("post_id", rs.getInt("post_id"));
                map.put("user_id", rs.getInt("user_id"));
                map.put("title", rs.getString("title"));
                map.put("content", rs.getString("content"));
                map.put("post_type", rs.getInt("post_type"));
                map.put("view_count", rs.getInt("view_count"));
                map.put("create_time", rs.getTimestamp("create_time"));
                map.put("update_time", rs.getTimestamp("update_time"));
                map.put("username", rs.getString("username"));
                map.put("reply_count", rs.getInt("reply_count"));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询用户的就业指导帖子总数
     */
    public int findCareerPostsTotalCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM post WHERE user_id = ? AND post_type = 3"; // 3=就业指导
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询用户已回复的就业指导帖子数量
     */
    public int findRepliedCareerCount(Integer userId) {
        String sql = "SELECT COUNT(DISTINCT p.post_id) FROM post p " +
                "INNER JOIN post_reply r ON p.post_id = r.post_id " +
                "WHERE p.user_id = ? AND p.post_type = 3"; // 3=就业指导
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询用户待回复的就业指导帖子数量
     */
    public int findWaitingCareerCount(Integer userId) {
        String sql = "SELECT COUNT(*) FROM post p " +
                "WHERE p.user_id = ? AND p.post_type = 3 " + // 3=就业指导
                "AND NOT EXISTS (SELECT 1 FROM post_reply r WHERE r.post_id = p.post_id)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}